package DAO;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.ClassModel;
import model.Course;
import util.DataUtil;

public class ClassDAO {

    public static List<ClassModel> getAll() {
        List<ClassModel> list = new ArrayList<ClassModel>();
        String call = "{Call GET_ALL_CLASS}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ClassModel cl = new ClassModel();
                cl.setClassId(rs.getString("ClassID"));
                cl.setClassName(rs.getString("ClassName"));
                int courseId = rs.getInt("CourseID");
                String courseName = rs.getString("CourseName");
                Course c = new Course();
                c.setCourseID(courseId);
                c.setCourseName(courseName);
                cl.setCourse(c);
                cl.setStartYear(rs.getInt("StartYear"));
                cl.setFinishYear(rs.getInt("FinishYear"));
                cl.setNoSemester(rs.getInt("NoOfSemester"));
                list.add(cl);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static List<ClassModel> getClassByCourse(int CourseID) {
        List<ClassModel> list = new ArrayList<ClassModel>();
        String call = "select * from Class where CourseID = ?";
        try {
            PreparedStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setInt(1, CourseID);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ClassModel cl = new ClassModel();
                cl.setClassId(rs.getString("ClassID"));
                cl.setClassName(rs.getString("ClassName"));
                list.add(cl);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean addClass(ClassModel cl) {
        int rs = 0;
        String call = "{Call ADD_CLASS(?,?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, cl.getClassId());
            cs.setString(2, cl.getClassName());
            cs.setInt(3, cl.getNoSemester());
            cs.setInt(4, cl.getStartYear());
            cs.setInt(5, cl.getFinishYear());
            cs.setInt(6, cl.getCourse().getCourseID());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean editClass(ClassModel cl) {
        int rs = 0;
        String call = "{Call EDIT_CLASS(?,?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, cl.getClassId());
            cs.setString(2, cl.getClassName());
            cs.setInt(3, cl.getNoSemester());
            cs.setInt(4, cl.getStartYear());
            cs.setInt(5, cl.getFinishYear());
            cs.setInt(6, cl.getCourse().getCourseID());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean checkClassID(String id) {
        boolean fl = true;
        String call = "{Call CHECK_CLASS_ID(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                fl = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean checkClassName(String name) {
        boolean fl = true;
        String call = "{Call CHECK_CLASS_NAME(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, name);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                fl = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean deleteClass(String classID) {
        int rs = 0;
        String call = "{Call DELETE_CLASS(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, classID);
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static List<ClassModel> search(String filter, String key, List<ClassModel> listClass) {
        List<ClassModel> list = new ArrayList<ClassModel>();
        for (ClassModel clm : listClass) {
            if (filter.equals("Class ID")) {
                if ((clm.getClassId().toLowerCase()).contains(key.toLowerCase())) {
                    list.add(clm);
                }
            }
            if (filter.equals("Class Name")) {
                if ((clm.getClassName().toLowerCase()).contains(key.toLowerCase())) {
                    list.add(clm);
                }
            }
            if (filter.equals("Course Name")) {
                if ((clm.getCourse().getCourseName().toLowerCase()).contains(key.toLowerCase())) {
                    list.add(clm);
                }
            }
            if (filter.equals("Start Year")) {
                if ((clm.getStartYear() + "".toLowerCase()).contains(key.toLowerCase())) {
                    list.add(clm);
                }
            }
            if (filter.equals("Finish Year")) {
                if ((clm.getFinishYear() + "".toLowerCase()).contains(key.toLowerCase())) {
                    list.add(clm);
                }
            }
        }
        return list;
    }
}
